﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace lab2
{
    public partial class bilets : Form
    {
        public bilets()
        {
            InitializeComponent();
        }

        OleDbConnection cn;
        OleDbCommand cm;
        OleDbDataAdapter da;
        DataTable dt;
        private void bilets_Load(object sender, EventArgs e)
        {
        }
        public void testc()
        {
            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:\\studying\\6sem\\БД\\Student\\passengers.mdb");
            cn.Open();
            cm = new OleDbCommand("SELECT Билеты.id_ticket as номер, Пассажиры.first_name as Имя, пассажиры.middle_name as отчество, Билеты.seat as место, Рейсы.to as куда FROM Рейсы INNER JOIN (Пассажиры INNER JOIN Билеты ON Пассажиры.id_passenger = Билеты.id_passenger) ON Рейсы.id_flight = Билеты.id_flight;", cn);
            da = new OleDbDataAdapter();
            da.SelectCommand = cm;
            dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            cn.Close();
            cm.Dispose();
            da.Dispose();
            dt.Dispose();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int id;
            if (dataGridView1.CurrentRow != null)
            {
                DataGridViewRow currentRow = dataGridView1.CurrentRow;
                id = Convert.ToInt32(currentRow.Cells[0].Value);
            }
            else
            {
                id = 0;
            }
            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:\\studying\\6sem\\БД\\Student\\passengers.mdb");
            cn.Open();
            string test = "DELETE * FROM Билеты WHERE Билеты.id_ticket = " + id;
            cm = new OleDbCommand(test, cn);
            cm.ExecuteNonQuery();
            cm.Dispose();
            cn.Close();
            cm.Dispose();
            da.Dispose();
            dt.Dispose();
            dataGridView1.Rows.RemoveAt(dataGridView1.CurrentRow.Index);
        }

        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            string name = "";
            switch (dataGridView1.Columns[e.ColumnIndex].Name)
            {
                case "место":
                    name = "seat";
                    break;


            }
            if (name == "")
            {
                errorForm frm = new errorForm();
                frm.Show();
            }
            else
            {
                cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:\\studying\\6sem\\БД\\Student\\passengers.mdb");
                cn.Open();

                string test = "UPDATE билеты SET `" + name + "`='" + dataGridView1[e.ColumnIndex, e.RowIndex].Value.ToString() + "' WHERE билеты.id_ticket=" + dataGridView1[0, e.RowIndex].Value.ToString();
                cm = new OleDbCommand(test, cn);
                cm.ExecuteNonQuery();
                cn.Close();
                cm.Dispose();
                da.Dispose();
                dt.Dispose();
            }
        }

    }
}
